﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Data.OracleClient;
using System.Data.OleDb;
using System.Data.Odbc;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Reflection;

namespace EEPClientDBSocket
{
    public partial class TableNameSelect : Form
    {
        public TableNameSelect()
        {
            InitializeComponent();
        }

        private IDbConnection _conn;
        public TableNameSelect(IDbConnection conn)
        {
            _conn = conn;
            InitializeComponent();
        }

        private void TableNameSelect_Load(object sender, EventArgs e)
        {
            this.lbTableName.DataSource = GetAllTablesList();
        }

        private List<String> GetAllTablesList()
        {
            List<String> tablesList = new List<string>();
            String sQL = "";
            if (_conn is SqlConnection)
            {
                sQL = "select @@version as version";
                IDbCommand cmd0 = AllocateCommand(sQL);
                if (_conn.State == ConnectionState.Closed)
                    _conn.Open();

                Object o = cmd0.ExecuteScalar();
                _conn.Close();
                if (o.ToString().ToLower().IndexOf("microsoft sql server 2005") >= 0)
                {
                    sQL = @"select (
                            case when b.name != 'dbo' then 
	                            case when (Charindex(' ',Rtrim(Ltrim(b.name)),0) > 0) then
		                            '[' + b.[name] + ']'
	                            else
		                            b.[name]
	                            end
	                            + '.' +
	                            case when (Charindex(' ',Rtrim(Ltrim(a.name)),0) != 0) then
		                            '[' + a.[name] + ']'
	                            else
		                            a.[name]
	                            end
                            else 
	                            case when (Charindex(' ',Rtrim(Ltrim(a.name)),0) != 0) then
		                            '[' + a.[name] + ']'
	                            else
		                            a.[name]
	                            end
                            end
                        )as name from sysobjects a,sys.schemas b where a.uid=b.schema_id and a.xtype in ('u','U','v','V') order by a.[name]";
                }
                else
                {
                    sQL = @"select(
                            case when (Charindex(' ',Rtrim(Ltrim(name)),0) != 0) then
		                        '[' + [name] + ']'
	                        else
		                        [name]
                            end
                            ) as name from sysobjects where xtype in ('u','U','v','V')  order by [name]";
                }
            }
            else if (_conn is OdbcConnection)
                sQL = "select * from systables where (tabtype = 'T' or tabtype = 'V') and tabid >= 100 order by tabname";
            else if (_conn is OracleConnection)
                sQL = "SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE' OR OBJECT_TYPE = 'VIEW'order by OBJECT_NAME";
            else if (_conn is OleDbConnection)
                sQL = "select * from sysobjects where xtype in ('u','U','v','V')  order by [name]";
            else if (_conn.GetType().Name == "MySqlConnection")
                sQL = "show tables;";
            else if (_conn.GetType().Name == "IfxConnection")
                sQL = "select * from systables where (tabtype = 'T' or tabtype = 'V') and tabid >= 100 order by tabname";

            IDbCommand cmd = AllocateCommand(sQL);

            if (_conn.State == ConnectionState.Closed)
            { _conn.Open(); }

            IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            while (reader.Read())
            {
                if (_conn is SqlConnection)
                    tablesList.Add(reader["name"].ToString());
                else if (_conn is OdbcConnection)
                    tablesList.Add(reader["tabname"].ToString());
                else if (_conn is OracleConnection)
                    tablesList.Add(reader["OBJECT_NAME"].ToString());
                else if (_conn is OleDbConnection)
                    tablesList.Add(reader["name"].ToString());
                else if (_conn.GetType().Name == "MySqlConnection")
                    tablesList.Add(reader["name"].ToString());
                else if (_conn.GetType().Name == "IfxConnection")
                    tablesList.Add(reader["tabname"].ToString());
            }
            _conn.Close();
            return tablesList;
        }

        private IDbCommand AllocateCommand(string sQL)
        {
            if (_conn is SqlConnection)
            {
                return new SqlCommand(sQL, (SqlConnection)_conn);
            }
            else if (_conn is OdbcConnection)
            {
                return new OdbcCommand(sQL, (OdbcConnection)_conn);
            }
            else if (_conn is OracleConnection)
            {
                return new OracleCommand(sQL, (OracleConnection)_conn);
            }
            else if (_conn is OleDbConnection)
            {
                return new OleDbCommand(sQL, (OleDbConnection)_conn);
            }
            else if (_conn.GetType().Name == "MySqlConnection")
            {
                String sPath = EEPRegistry.Server + "\\MySql.Data.dll";
                Assembly assembly = Assembly.LoadFrom(sPath);
                IDbCommand Result = assembly.CreateInstance("MySql.Data.MySqlClient.MySqlCommand") as IDbCommand;
                Result.Connection = _conn;
                Result.CommandText = sQL;
                return Result;
            }
            else if (_conn.GetType().Name == "IfxConnection")
            {
                String sPath = EEPRegistry.Server + "\\IBM.Data.Informix.dll";
                Assembly assembly = Assembly.LoadFrom(sPath);
                IDbCommand Result = assembly.CreateInstance("IBM.Data.Informix.IfxCommand") as IDbCommand;
                Result.Connection = _conn;
                Result.CommandText = sQL;
                return Result;
            }
            else return null;
        }

        public String TableName;
        private void btnOK_Click(object sender, EventArgs e)
        {
            if (this.lbTableName.SelectedIndex > -1)
                TableName = this.lbTableName.SelectedItem.ToString();
            this.Close();
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}